"""
# -*- coding:utf-8 -*-
@Project : nicegui
@software: PyCharm
@File : db_connect.py
@author:  wade
@IDE NAME: PyCharm
@Time : 2025/6/5 12:32
@
"""
# pip install PyMySQL
import pymysql


class MySQLCRUD:
    def __init__(self):
        self.connection = pymysql.connect(
            host='82.157.248.21',
            user='test_dev',
            password='test_dev',
            database='videos',
            cursorclass=pymysql.cursors.DictCursor
        )

    def create(self, table, data):
        """插入数据"""
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = f"INSERT INTO {table} ({keys}) VALUES ({values})"
        with self.connection.cursor() as cursor:
            cursor.execute(sql, tuple(data.values()))
            self.connection.commit()
        return cursor.lastrowid

    def read(self, table, columns='*', where=None, params=None):
        """查询数据"""
        sql = f"SELECT {columns} FROM {table}"
        if where:
            sql += f" WHERE {where}"
        with self.connection.cursor() as cursor:
            print(sql)
            cursor.execute(sql)

            return cursor.fetchall()

    def update(self, table, data, where, params=None):
        """更新数据"""
        set_clause = ', '.join([f"{k}=%s" for k in data.keys()])
        sql = f"UPDATE {table} SET {set_clause} WHERE {where}"
        with self.connection.cursor() as cursor:
            cursor.execute(sql, tuple(data.values()) + (params or ()))
            self.connection.commit()
        return cursor.rowcount

    def delete(self, table, where, params=None):
        """删除数据"""
        sql = f"DELETE FROM {table} WHERE {where}"
        with self.connection.cursor() as cursor:
            cursor.execute(sql, params or ())
            self.connection.commit()
        return cursor.rowcount

    def __del__(self):
        """析构时自动关闭连接"""
        self.connection.close()

# # 使用示例
# db = MySQLCRUD('localhost', 'root', 'password', 'test_db')
#
# # 插入数据
# new_id = db.create('users', {'name': 'Alice', 'age': 25})
# print(f"插入ID: {new_id}")
#
# # 查询数据
# users = db.read('users', where='age > %s', params=(20,))
# print("查询结果:", users)
#
# # 更新数据
# updated = db.update('users', {'age': 26}, 'id = %s', (new_id,))
# print(f"更新了{updated}条记录")
#
# # 删除数据
# deleted = db.delete('users', 'id = %s', (new_id,))
# print(f"删除了{deleted}条记录")
